/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50717 (5.7.17)
 Source Host           : localhost:3306
 Source Schema         : school-demo

 Target Server Type    : MySQL
 Target Server Version : 50717 (5.7.17)
 File Encoding         : 65001

 Date: 08/12/2022 17:31:40
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `cno` varchar(20) NOT NULL COMMENT '课程名称',
  `cname` varchar(256) NOT NULL COMMENT '用户名',
  `tno` varchar(20) NOT NULL COMMENT '教工编号(外键)',
  PRIMARY KEY (`cno`),
  KEY `tno` (`tno`),
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`tno`) REFERENCES `teacher` (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='`课程表`';

-- ----------------------------
-- Records of course
-- ----------------------------
BEGIN;
INSERT INTO `course` (`cno`, `cname`, `tno`) VALUES ('19-888', '高等数学', '831');
INSERT INTO `course` (`cno`, `cname`, `tno`) VALUES ('3-105', '计算机导论', '825');
INSERT INTO `course` (`cno`, `cname`, `tno`) VALUES ('3-245', '操作系统', '804');
INSERT INTO `course` (`cno`, `cname`, `tno`) VALUES ('6-166', '数据电路', '856');
COMMIT;

-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
  `low` tinyint(4) DEFAULT NULL,
  `upp` tinyint(4) DEFAULT NULL,
  `rank` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of grade
-- ----------------------------
BEGIN;
INSERT INTO `grade` (`low`, `upp`, `rank`) VALUES (90, 100, 'A');
INSERT INTO `grade` (`low`, `upp`, `rank`) VALUES (80, 89, 'B');
INSERT INTO `grade` (`low`, `upp`, `rank`) VALUES (70, 79, 'C');
INSERT INTO `grade` (`low`, `upp`, `rank`) VALUES (60, 69, 'D');
INSERT INTO `grade` (`low`, `upp`, `rank`) VALUES (0, 59, 'E');
COMMIT;

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sno` varchar(20) NOT NULL COMMENT '学号（外码）',
  `cno` varchar(20) NOT NULL COMMENT '课程号（外码）',
  `degree` decimal(4,1) DEFAULT NULL COMMENT '成绩',
  KEY `studentno` (`sno`),
  KEY `courseno` (`cno`),
  CONSTRAINT `courseno` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`),
  CONSTRAINT `studentno` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='`成绩表`';

-- ----------------------------
-- Records of score
-- ----------------------------
BEGIN;
INSERT INTO `score` (`sno`, `cno`, `degree`) VALUES ('103', '3-245', 86.0);
INSERT INTO `score` (`sno`, `cno`, `degree`) VALUES ('105', '3-245', 75.0);
INSERT INTO `score` (`sno`, `cno`, `degree`) VALUES ('109', '3-245', 68.0);
INSERT INTO `score` (`sno`, `cno`, `degree`) VALUES ('103', '3-105', 92.0);
INSERT INTO `score` (`sno`, `cno`, `degree`) VALUES ('105', '3-105', 88.0);
INSERT INTO `score` (`sno`, `cno`, `degree`) VALUES ('109', '3-105', 76.0);
INSERT INTO `score` (`sno`, `cno`, `degree`) VALUES ('101', '3-105', 64.0);
INSERT INTO `score` (`sno`, `cno`, `degree`) VALUES ('107', '3-105', 91.0);
INSERT INTO `score` (`sno`, `cno`, `degree`) VALUES ('108', '3-105', 78.0);
INSERT INTO `score` (`sno`, `cno`, `degree`) VALUES ('101', '6-166', 85.0);
INSERT INTO `score` (`sno`, `cno`, `degree`) VALUES ('107', '6-166', 79.0);
INSERT INTO `score` (`sno`, `cno`, `degree`) VALUES ('108', '6-166', 81.0);
COMMIT;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sno` varchar(20) NOT NULL COMMENT '学号',
  `sname` varchar(20) NOT NULL COMMENT '学生姓名',
  `ssex` varchar(20) NOT NULL COMMENT '学生性别',
  `sbirthday` datetime DEFAULT NULL COMMENT '学生出生年月',
  `class` varchar(20) DEFAULT NULL COMMENT '学生所在班级',
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='`学生表`';

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` (`sno`, `sname`, `ssex`, `sbirthday`, `class`) VALUES ('101', '李军', '男', '1976-02-20 00:00:00', '95033');
INSERT INTO `student` (`sno`, `sname`, `ssex`, `sbirthday`, `class`) VALUES ('103', '陆君', '男', '1974-06-03 00:00:00', '95031');
INSERT INTO `student` (`sno`, `sname`, `ssex`, `sbirthday`, `class`) VALUES ('105', '匡明', '男', '1975-10-02 00:00:00', '95031');
INSERT INTO `student` (`sno`, `sname`, `ssex`, `sbirthday`, `class`) VALUES ('107', '王丽', '女', '1976-01-23 00:00:00', '95033');
INSERT INTO `student` (`sno`, `sname`, `ssex`, `sbirthday`, `class`) VALUES ('108', '曾华', '男', '1977-09-01 00:00:00', '95033');
INSERT INTO `student` (`sno`, `sname`, `ssex`, `sbirthday`, `class`) VALUES ('109', '王芳', '女', '1975-02-10 00:00:00', '95031');
COMMIT;

-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `tno` varchar(20) NOT NULL COMMENT '教工编号（主码）',
  `tname` varchar(20) NOT NULL COMMENT '教工姓名',
  `tsex` varchar(20) NOT NULL COMMENT '教工性别',
  `tbirthday` datetime DEFAULT NULL COMMENT '教工出生年月',
  `prof` varchar(20) DEFAULT NULL COMMENT '职称',
  `depart` varchar(20) NOT NULL COMMENT '教工所在部门',
  PRIMARY KEY (`tno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='`教师表`';

-- ----------------------------
-- Records of teacher
-- ----------------------------
BEGIN;
INSERT INTO `teacher` (`tno`, `tname`, `tsex`, `tbirthday`, `prof`, `depart`) VALUES ('804', '李诚', '男', '1958-12-02 00:00:00', '副教授', '计算机系');
INSERT INTO `teacher` (`tno`, `tname`, `tsex`, `tbirthday`, `prof`, `depart`) VALUES ('825', '王萍', '女', '1972-05-05 00:00:00', '助教', '计算机系');
INSERT INTO `teacher` (`tno`, `tname`, `tsex`, `tbirthday`, `prof`, `depart`) VALUES ('831', '刘冰', '女', '1977-08-14 00:00:00', '助教', '电子工程系');
INSERT INTO `teacher` (`tno`, `tname`, `tsex`, `tbirthday`, `prof`, `depart`) VALUES ('856', '张旭', '男', '1969-03-12 00:00:00', '讲师', '电子工程系');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
